var scripts = {
  /** 显示数据库状态。*/
  ShowStatus: "show status",

  /** 显示全部进程 */
  ShowProcessList: "show full processlist;",

  /** 获取数据库列表。 */
  DataBases: `select
      SCHEMA_NAME as Name
    from information_schema.SCHEMATA
    where LOWER(SCHEMA_NAME) not in('information_schema', 'mysql', 'performance_schema', 'sys')`,

  /** 获取表信息 */
  Tables: `select
    table_name as table_name,
    table_comment as table_comment,
    table_rows as table_rows,
    avg_row_length as avg_row_length, 
    case
      when data_length>1024 and data_length < 1024*1024 then concat(floor(data_length/1024), ' KB')
      when data_length>1024*1024 and data_length < 1024*1024*1024 then concat(format(data_length/(1024*1024), 2), ' MB')
      when data_length>1024*1024*1024 then concat(format(data_length/(1024*1024*1024), 2), ' GB')
      else data_length
    end as used,
    case
      when data_free>1024 and data_free < 1024*1024 then concat(floor(data_free/1024), ' KB')
      when data_free>1024*1024 and data_free < 1024*1024*1024 then concat(format(data_free/(1024*1024), 2), ' MB')
      when data_free>1024*1024*1024 then concat(format(data_free/(1024*1024*1024), 2), ' GB')
    else data_free
    end as data_free,
    index_length as index_length
  from information_schema.TABLES where TABLE_SCHEMA=@dbName and table_type='BASE TABLE'`,

  /** 获取表的创建语句 */
  TableDDL: "show create table #dbName.#tableName",

  /** 获取表的字段信息 */
  TableColumns: `select column_name as column_name,
    column_comment as column_comment,
    column_type as column_type,
    is_nullable as is_nullable,
    column_key as column_key,
    column_default as column_default, 
    table_name as tableName,
    CASE
      WHEN character_maximum_length IS NOT NULL THEN character_maximum_length
      WHEN data_type='timestamp' OR data_type='datetime' OR data_type='date' OR data_type='time' THEN 160
      WHEN data_type='bigint' OR data_type='decimal' OR data_type='int' THEN 120
    END character_length 
    from information_schema.columns 
    where TABLE_SCHEMA=@dbName and table_name=@tableName order by ordinal_position asc`,

  /** 获取表数据 */
  TableDatas: "select * from #dbName.#tableName",

  /** 查询总记录数 */
  TableDatasCount: "SELECT FOUND_ROWS() AS totals;",

  /** 查询数据库的所有索引 */
  TablesIndexs: `SELECT
          table_name as "table_name",
          index_name as "index_name",
          GROUP_CONCAT(column_name ORDER BY column_name ASC) AS columns
        FROM information_schema.\`STATISTICS\` a
        WHERE table_schema=@dbName
        GROUP BY table_name, index_name`
};

module.exports = scripts;